package com.jnkj.demo.Dao;

import com.jnkj.demo.pojo.Classess;
import com.jnkj.demo.pojo.Students;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
@Repository
public class ClassesDaoImpl implements ClassesDao{
    @Autowired
    private NamedParameterJdbcTemplate jdbcTemplate;
    //1
    @Override
    public int addClasses(Classess classes) {
        String sql="insert into classes(sbanji,banzhuren,kskc,kbrq)"+
                "values(:sbanji,:banzhuren,:kskc,:kbrq)";
        Map<String,Object> map=new HashMap<>();
        map.put("sbanji",classes.getSbanji());
        map.put("banzhuren",classes.getBanzhuren());
        map.put("kskc",classes.getKskc());
        map.put("kbrq",classes.getKbrq());
        return jdbcTemplate.update(sql,map);
    }
    //4
    @Override
    public List select4() {
        String sql="select * from classes where  FIND_IN_SET('日语',kskc)";
        List<Classess> classessList = jdbcTemplate.query(sql, new RowMapper<Classess>(){

            @Override
            public Classess mapRow(ResultSet rs, int rowNum) throws SQLException {
                Classess classes=new Classess();
                classes.setSbanji(rs.getString("sbanji"));
                classes.setBanzhuren(rs.getString("banzhuren"));
                classes.setKskc(rs.getString("kskc"));
                classes.setKbrq(rs.getString("kbrq"));
                return classes;
            }
        });
        for(Classess classes : classessList){

            System.out.println(classes.getSbanji()+ "  "+  classes.getBanzhuren()+ "  "+ classes.getKskc()+ "  "+ classes.getKbrq());
        }
        return null;
    }
    //15
    @Override
    public int updClasses(Classess classes) {
        String sql="update classes c,students s set c.kbrq='2022-05-26' where c.sbanji=s.sbanji and c.sbanji=(select sbanji from students where sname=:sname)";
        Map<String,Object> map=new HashMap<>();
        map.put("sname",classes.getSname());
        return jdbcTemplate.update(sql,map);
    }
//9
    @Override
    public List select9() {
        String sql="select c.*,s.sname from classes c,students s where c.sbanji=s.sbanji and c.sbanji='计算机2班'";
        List<Classess> classesList=jdbcTemplate.query(sql, new RowMapper<Classess>() {
            @Override
            public Classess mapRow(ResultSet rs, int rowNum) throws SQLException {
                Classess classes=new Classess();
                classes.setSbanji(rs.getString("sbanji"));
                classes.setBanzhuren(rs.getString("banzhuren"));
                classes.setKskc(rs.getString("kskc"));
                classes.setKbrq(rs.getString("kbrq"));
                classes.setSname(rs.getString("sname"));
                return classes;
            }
        });
        for(Classess classes:classesList){
            System.out.println(classes.getSbanji()+" "+classes.getBanzhuren()+" "+classes.getKskc()+" "+classes.getKbrq()+" "+classes.getSname());
        }
        return null;
    }
//19
    @Override
    public int updClasses19(Classess classes) {
        String sql="update classes set kskc=CONCAT(kskc,',德语') where sbanji=:sbanji";
        Map<String,Object> map=new HashMap<>();
        map.put("sbanji",classes.getSbanji());
        return jdbcTemplate.update(sql,map);
    }

    @Override
    public int updClasses200(Classess classes) {
        String sql="update classes set kskc=CONCAT(kskc,',德语') where sbanji=:sbanji";
        Map<String,Object> map=new HashMap<>();
        map.put("sbanji",classes.getSbanji());
        return jdbcTemplate.update(sql,map);
    }
}
